USE MASTER
GO
IF EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME = 'Blue_Pumpkin')
DROP DATABASE Blue_Pumpkin
GO

CREATE DATABASE Blue_Pumpkin
GO

USE Blue_Pumpkin
GO


CREATE TABLE [Admin]
(	
	Username varchar(50) PRIMARY KEY,
	[Password] varchar(50) NOT NULL DEFAULT '123456',
	Email varchar(50) NOT NULL UNIQUE,
	Fullname nvarchar(50) NOT NULL,
	Gender nvarchar(20) NOT NULL CHECK(Gender = 'Male' OR Gender = 'Female'),
	Birthdate smalldatetime NOT NULL,	
	Phone nvarchar(20) NOT NULL,
	Mobilephone nvarchar(20) NOT NULL,
	[Address] nvarchar(100) NOT NULL,
	IdentityCard varchar(20) NOT NULL,
	JoinInDate smalldatetime DEFAULT GETDATE(),
	Position nvarchar(100),
	Departments nvarchar(100),	
	Groups varchar(200),
	[Status] varchar(20) DEFAULT 'Working',
	[Online] bit DeFAULT 0 CHECK([Online] = 0 or [Online] = 1),	
	Logged smalldatetime DEFAULT GETDATE()
)
GO

CREATE TABLE Employee(	
	Username varchar(50) PRIMARY KEY,
	[Password] varchar(50) NOT NULL DEFAULT '123456',
	Email varchar(50) NOT NULL UNIQUE,
	Fullname nvarchar(50) NOT NULL,
	Gender nvarchar(20) NOT NULL CHECK(Gender = 'Male' OR Gender = 'Female'),
	Birthdate smalldatetime NOT NULL,	
	Phone nvarchar(20) NOT NULL,
	Mobilephone nvarchar(20) NOT NULL,
	[Address] nvarchar(100) NOT NULL,
	IdentityCard varchar(20) NOT NULL,
	JoinInDate smalldatetime DEFAULT GETDATE(),
	Position nvarchar(100),
	Departments nvarchar(100),	
	[Groups] varchar(200),
	[Status] varchar(20) DEFAULT 'Working',
	[Online] bit DeFAULT 0 CHECK([Online] = 0 or [Online] = 1),	
	Logged smalldatetime DEFAULT GETDATE(),
	Avatar varchar(200)
)
GO

CREATE TABLE PictureLibrary
(
	Id int PRIMARY KEY IDENTITY(1,1),
	Username varchar(50) FOREIGN KEY REFERENCES Employee(Username) NOT NULL,
	PictureURL nvarchar(MAX) NOT NULL,
	Album nvarchar(200) NOT NULL DEFAULT 'Profile Pictures',
)
GO

CREATE TABLE EventLibrary
(
	EventId int PRIMARY KEY IDENTITY(1,1),
	Name nvarchar(200) NOT NULL,
	[Description] nvarchar(MAX),
	EventType nvarchar(100) DEFAULT 'Talk Show',
	TimeStart smalldatetime NOT NULL,
	TimeEnd smalldatetime NOT NULL
)
GO
CREATE TABLE EventComment
(
	Id int PRIMARY KEY IDENTITY(1,1),
	EventId int FOREIGN KEY REFERENCES EventLibrary(EventId) NOT NULL,
	Username varchar(50) FOREIGN KEY REFERENCES Employee(Username) NOT NULL,
	Comment nvarchar(MAX),
	Commented smalldatetime DEFAULT GETDATE() NOT NULL	
)
GO

CREATE TABLE EventRegistration
(
	Id int PRIMARY KEY IDENTITY(1,1),
	EventId int FOREIGN KEY REFERENCES EventLibrary(EventId) NOT NULL,
	Username varchar(50) FOREIGN KEY REFERENCES Employee(Username) NOT NULL,
	TimeRegisted smalldatetime DEFAULT GETDATE() NOT NULL,
)
GO
CREATE TABLE EventGift
(
	Id int PRIMARY KEY IDENTITY(1,1),
	EventId int FOREIGN KEY REFERENCES EventLibrary(EventId) NOT NULL,
	Username varchar(50) FOREIGN KEY REFERENCES Employee(Username),
	GiftType varchar(10) CHECK(GiftType='1st'OR GiftType='2nd' OR GiftType='3rd'OR GiftType='4th' OR GiftType='everybody'),
	GiftValue varchar(100)
)
GO

-- ContactMessage: Employee send feedback message to adminstrator, if admin ever read yet => Recieved = 0, contrary Recieved = 1.
-- if, admin want to reply to employee, we will use Java Mail.
CREATE TABLE ContactMessage(
	Id int PRIMARY KEY IDENTITY(1,1),
	Username varchar(50) FOREIGN KEY REFERENCES Employee(Username) NOT NULL,
	MessageContent nvarchar(MAX),
	Recieved bit DEFAULT 0 CHECK(Recieved = 0 or Recieved = 1),
	TimeSent smalldatetime DEFAULT GETDATE()	
)
GO
CREATE TABLE FAQ(
	Id int PRIMARY KEY IDENTITY(1,1),
	Question nvarchar(200) Not Null,
	Answer nvarchar(200) Not Null
)
GO

CREATE TABLE EventInfo
(
	Id int PRIMARY KEY IDENTITY(1,1),
	EventId int FOREIGN KEY REFERENCES EventLibrary(EventId) NOT NULL,
	AvatarImg nvarchar(200) NOT NULL,
	EventShortDesc nvarchar(200) NOT NULL,
	Created smalldatetime DEFAULT GETDATE(),
	[Address] nvarchar(1000)
)
GO


CREATE TABLE NotificationType
(
  Id int PRIMARY KEY IDENTITY(1,1),
  Destype nvarchar(100)
)
GO

CREATE TABLE Notifications
(
 Id int PRIMARY KEY IDENTITY(1,1),
 NotificationTypeID int FOREIGN KEY REFERENCES NotificationType(Id),
 UsernameME varchar(50) FOREIGN KEY REFERENCES Employee(Username) ,
 UsernameFE varchar(50) FOREIGN KEY REFERENCES Employee(Username) ,
 EventId int FOREIGN KEY REFERENCES EventLibrary(EventId),
 [Status] Nvarchar(1)DEFAULT 'N' Check ([Status] ='Y' OR [Status] ='N'),
 AvatarImg nvarchar(200) ,
 Fullname nvarchar(50),
 [Message] nvarchar(600),
 NameEvent nvarchar(200),
 Birthdate smalldatetime,	
)
GO
 
